#!/usr/bin/env bash
set -euo pipefail

PARQUET="data/边境城市七普乡镇.parquet"

duckdb <<'SQL'
-- load parquet into a temporary table
CREATE TABLE tmp AS SELECT * FROM read_parquet('data/边境城市七普乡镇.parquet');

-- Numeric columns: 年份, 地区等级, 总人口, 年龄0_14岁, 年龄15_59岁, 年龄60岁级以上, 年龄65岁级以上, 居住或户口在本乡镇街道, border_city, border_county
-- For each numeric column compute: total, nonnull, nulls, min, median, mean, sd, distinct
SELECT '年份' AS col, count(*) AS total, count(年份) AS nonnull, count(*)-count(年份) AS nulls, ROUND(min(年份),2) AS min, ROUND(quantile(年份,0.5),2) AS median, ROUND(avg(年份),2) AS mean, ROUND(stddev(年份),2) AS sd, count(DISTINCT 年份) AS distinct_vals FROM tmp;
SELECT '地区等级' AS col, count(*) AS total, count(地区等级) AS nonnull, count(*)-count(地区等级) AS nulls, ROUND(min(地区等级),2) AS min, ROUND(quantile(地区等级,0.5),2) AS median, ROUND(avg(地区等级),2) AS mean, ROUND(stddev(地区等级),2) AS sd, count(DISTINCT 地区等级) AS distinct_vals FROM tmp;
SELECT '总人口' AS col, count(*) AS total, count(总人口) AS nonnull, count(*)-count(总人口) AS nulls, ROUND(min(总人口),2) AS min, ROUND(quantile(总人口,0.5),2) AS median, ROUND(avg(总人口),2) AS mean, ROUND(stddev(总人口),2) AS sd, count(DISTINCT 总人口) AS distinct_vals FROM tmp;
SELECT '年龄0_14岁' AS col, count(*) AS total, count(年龄0_14岁) AS nonnull, count(*)-count(年龄0_14岁) AS nulls, ROUND(min(年龄0_14岁),2) AS min, ROUND(quantile(年龄0_14岁,0.5),2) AS median, ROUND(avg(年龄0_14岁),2) AS mean, ROUND(stddev(年龄0_14岁),2) AS sd, count(DISTINCT 年龄0_14岁) AS distinct_vals FROM tmp;
SELECT '年龄15_59岁' AS col, count(*) AS total, count(年龄15_59岁) AS nonnull, count(*)-count(年龄15_59岁) AS nulls, ROUND(min(年龄15_59岁),2) AS min, ROUND(quantile(年龄15_59岁,0.5),2) AS median, ROUND(avg(年龄15_59岁),2) AS mean, ROUND(stddev(年龄15_59岁),2) AS sd, count(DISTINCT 年龄15_59岁) AS distinct_vals FROM tmp;
SELECT '年龄60岁级以上' AS col, count(*) AS total, count(年龄60岁级以上) AS nonnull, count(*)-count(年龄60岁级以上) AS nulls, ROUND(min(年龄60岁级以上),2) AS min, ROUND(quantile(年龄60岁级以上,0.5),2) AS median, ROUND(avg(年龄60岁级以上),2) AS mean, ROUND(stddev(年龄60岁级以上),2) AS sd, count(DISTINCT 年龄60岁级以上) AS distinct_vals FROM tmp;
SELECT '年龄65岁级以上' AS col, count(*) AS total, count(年龄65岁级以上) AS nonnull, count(*)-count(年龄65岁级以上) AS nulls, ROUND(min(年龄65岁级以上),2) AS min, ROUND(quantile(年龄65岁级以上,0.5),2) AS median, ROUND(avg(年龄65岁级以上),2) AS mean, ROUND(stddev(年龄65岁级以上),2) AS sd, count(DISTINCT 年龄65岁级以上) AS distinct_vals FROM tmp;
SELECT '居住或户口在本乡镇街道' AS col, count(*) AS total, count(居住或户口在本乡镇街道) AS nonnull, count(*)-count(居住或户口在本乡镇街道) AS nulls, ROUND(min(居住或户口在本乡镇街道),2) AS min, ROUND(quantile(居住或户口在本乡镇街道,0.5),2) AS median, ROUND(avg(居住或户口在本乡镇街道),2) AS mean, ROUND(stddev(居住或户口在本乡镇街道),2) AS sd, count(DISTINCT 居住或户口在本乡镇街道) AS distinct_vals FROM tmp;
SELECT 'border_city' AS col, count(*) AS total, count(border_city) AS nonnull, count(*)-count(border_city) AS nulls, ROUND(min(border_city),2) AS min, ROUND(quantile(border_city,0.5),2) AS median, ROUND(avg(border_city),2) AS mean, ROUND(stddev(border_city),2) AS sd, count(DISTINCT border_city) AS distinct_vals FROM tmp;
SELECT 'border_county' AS col, count(*) AS total, count(border_county) AS nonnull, count(*)-count(border_county) AS nulls, ROUND(min(border_county),2) AS min, ROUND(quantile(border_county,0.5),2) AS median, ROUND(avg(border_county),2) AS mean, ROUND(stddev(border_county),2) AS sd, count(DISTINCT border_county) AS distinct_vals FROM tmp;

-- Text columns: 乡镇, 县, 市, 省 -> basic counts + top 5 frequent values
SELECT '乡镇' AS col, count(*) AS total, count(乡镇) AS nonnull, count(*)-count(乡镇) AS nulls, count(DISTINCT 乡镇) AS distinct_vals FROM tmp;
SELECT 乡镇 AS value, count(*) AS freq FROM tmp GROUP BY 乡镇 ORDER BY freq DESC LIMIT 5;

SELECT '县' AS col, count(*) AS total, count(县) AS nonnull, count(*)-count(县) AS nulls, count(DISTINCT 县) AS distinct_vals FROM tmp;
SELECT 县 AS value, count(*) AS freq FROM tmp GROUP BY 县 ORDER BY freq DESC LIMIT 5;

SELECT '市' AS col, count(*) AS total, count(市) AS nonnull, count(*)-count(市) AS nulls, count(DISTINCT 市) AS distinct_vals FROM tmp;
SELECT 市 AS value, count(*) AS freq FROM tmp GROUP BY 市 ORDER BY freq DESC LIMIT 5;

SELECT '省' AS col, count(*) AS total, count(省) AS nonnull, count(*)-count(省) AS nulls, count(DISTINCT 省) AS distinct_vals FROM tmp;
SELECT 省 AS value, count(*) AS freq FROM tmp GROUP BY 省 ORDER BY freq DESC LIMIT 5;

-- finalize
SELECT count(*) AS total_rows FROM tmp;
DROP TABLE tmp;
SQL

echo "DuckDB describe finished."
